package com.fenrirtec.aepp.common.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.fenrirtec.aepp.common.dao.TenderBidDao;
import com.fenrirtec.aepp.common.dto.TenderBidDto;
import com.fenrirtec.aepp.common.model.TenderBidlist;
import com.fenrirtec.core.database.DatabaseSessionManager;
import com.fenrirtec.core.database.ResultSetMapper;
import com.fenrirtec.core.exception.DatabaseException;

public class TenderBidDao_JDBCImpl implements TenderBidDao {
	private static final Logger logger = LoggerFactory
			.getLogger(TenderBidDao_JDBCImpl.class);
	
	@Override
	public List<TenderBidlist> findTenderBidlsit(Integer tenderid) {
		if(logger.isInfoEnabled()){
			logger.info("[TenderBidDao_JDBCImpl#findTenderBidlsit] start.");
		}
		PreparedStatement statement=null;
		try{
	
			StringBuffer sql=new StringBuffer()
			.append("select \n")
			.append("m.enterprise_name, \n")
			.append("m.enterprise_category_name, \n")			
			.append("m.area_code, \n")
			.append("m.rank_name, \n")			
			.append("t.total_price, \n")
			.append("(SELECT path FROM t_tender_bid_doc WHERE tender_id = t.tender_id and member_login_name=t.member_login_name) AS path, \n")			
			.append("(SELECT status_name FROM v_tender_information WHERE tender_id= t.tender_id ) AS status_name, \n")		
			.append("t.member_login_name, \n")
			.append("t.tender_id \n")		
			.append("from \n")
			.append(" v_member m \n")			
			.append(" ,t_tender_quote t \n")
			.append(" where \n")
			.append(" m.member_login_name=t.member_login_name \n")			
			.append(" and t.tender_id= ? \n")
			.append(" and t.bid_flag= false \n")
			.append(" and t.delete_flag= false \n");
			if (logger.isTraceEnabled()) {
				logger.trace("[TenderBidDao_JDBCImpl#findTenderBidlsit] sql={}", sql);
			}
			
			Connection connection = DatabaseSessionManager.getInstance().getSession();
			statement = connection.prepareStatement(sql.toString());
			statement.setInt(1, tenderid);
			ResultSet resultSet = statement.executeQuery();			
			ResultSetMapper<TenderBidlist> resultSetMapper=new ResultSetMapper<TenderBidlist>();
			List<TenderBidlist> tenderBidList=resultSetMapper.mapResultSetToList(resultSet, TenderBidlist.class);
			return tenderBidList;
		}catch(SQLException e){
			if (logger.isTraceEnabled()) {
				logger.trace("[TenderBidDao_JDBCImpl#findTenderBidlsit] sql error occurred.", e);
			}
			throw new DatabaseException("database error occurred.", e);
		}finally{
			if (logger.isInfoEnabled()) {
				logger.info("[TenderBidDao_JDBCImpl#findTenderBidlsit] finish.");
			}
			if (statement != null) { 
				try { 
					statement.close(); 
					} catch (SQLException e) {} 
				}
		}
		
	
	}

	@Override
	public void insertTenderBid(List<TenderBidDto> tenderBid) {
		if(logger.isInfoEnabled()){
			logger.info("[TenderBidDao_JDBCImpl#insertTenderBid] start.");
		}
		PreparedStatement statement=null;
		try{
			for(int i = 0;i < tenderBid.size();i++){
			
			StringBuffer sql=new StringBuffer()
			.append("insert into t_tender_bid( \n")
			.append(" tender_id,\n")
			.append(" member_login_name,\n")
			.append(" bid_date,\n")
			.append(" evaluation,\n")
			.append(" create_user,\n")
			.append(" create_date,\n")
			.append(" update_user,\n")
			.append(" update_date)\n")
			.append("values( \n")
			.append(" ? ,\n")
			.append(" ? ,\n")
			.append(" ? ,\n")
			.append(" ? ,\n")
			.append(" ? ,\n")
			.append(" ? ,\n")
			.append(" ? ,\n")
			.append(" ? )\n");
			if(logger.isTraceEnabled()){
				logger.trace("[TenderBidDao_JDBCImpl#insertTenderBid] sql={}",sql);
			}
			Connection connection = DatabaseSessionManager.getInstance()
					.getSession();
			statement = connection.prepareStatement(sql.toString());
			
			statement.setInt(1, tenderBid.get(i).getTenderId());
			statement.setString(2, tenderBid.get(i).getMemberLoginName());
			String bidDatestr = tenderBid.get(i).getBidDate();
			String createDatestr = tenderBid.get(i).getCreateDate();
			String updateDatestr = tenderBid.get(i).getUpdateDate();
			SimpleDateFormat sim=new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");	
			Date bidDate=null;
			Date createDate=null;
			Date updateDate=null;
			try {
				bidDate=sim.parse(bidDatestr);
				createDate=sim.parse(createDatestr);
				updateDate=sim.parse(updateDatestr);
			} catch (ParseException e) {
				
				e.printStackTrace();
			}
			
			
			statement.setDate(3,  new java.sql.Date(bidDate.getTime()));
			statement.setString(4, tenderBid.get(i).getEvaluation());
			statement.setString(5, "system");
			statement.setDate(6, new java.sql.Date(createDate.getTime()));
			statement.setString(7, "system");
			statement.setDate(8, new java.sql.Date(updateDate.getTime()));
			statement.execute();
			}
		}catch(SQLException e){
			if(logger.isTraceEnabled()){
				logger.trace("[TenderBidDao_JDBCImpl#insertTenderBid] sql error occurred.",
						e);
			}
			throw new DatabaseException("database error occurred.", e);
		}finally{
			if (logger.isInfoEnabled()) {
				logger.info("[TenderBidDao_JDBCImpl#insertTenderBid] finish.");
			}
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
				}
			}
		}

	}

	@Override
	public void updateTenderFinalist(Integer tenderid,String menmberName) {
		if(logger.isInfoEnabled()){
			logger.info("[TenderBidDao_JDBCImpl#updateTenderFinalist] start.");
		}
		PreparedStatement statement=null;
		try{
	
			StringBuffer sql=new StringBuffer()
			.append("update \n")
			.append(" t_tender_finalist \n")
			.append(" set \n")
			.append(" quote_flag = ? \n")
			.append(" where \n")
			.append(" tender_id = ?\n")
			.append(" and member_login_name = ?\n");
			
			if(logger.isTraceEnabled()){
				logger.trace("[TenderBidDao_JDBCImpl#updateTenderFinalist] sql={}",sql);
			}
			Connection connection = DatabaseSessionManager.getInstance()
					.getSession();
			statement = connection.prepareStatement(sql.toString());
			statement.setBoolean(1, true);
			statement.setInt(2, tenderid);
			statement.setString(3, menmberName);
			statement.executeUpdate();
			
		}catch(SQLException e){
			if(logger.isTraceEnabled()){
				logger.trace("[TenderBidDao_JDBCImpl#updateTenderFinalist] sql error occurred.",
						e);
			}
			throw new DatabaseException("database error occurred.", e);
		}finally{
			if (logger.isInfoEnabled()) {
				logger.info("[TenderBidDao_JDBCImpl#updateTenderFinalist] finish.");
			}
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
				}
			}
		}

	}

	@Override
	public List<TenderBidlist> findTenderBidSuppliers(Integer tenderid) {
		if(logger.isInfoEnabled()){
			logger.info("[TenderBidDao_JDBCImpl#findTenderBidSuppliers] start.");
		}
		PreparedStatement statement=null;
		try{
			StringBuffer sql=new StringBuffer()
			.append("select \n")
			.append("m.enterprise_name, \n")
			.append("m.enterprise_category_name, \n")			
			.append("m.area_code, \n")
			.append("m.rank_name, \n")			
			.append("(SELECT total_price FROM t_tender_quote WHERE tender_id = t.tender_id and member_login_name=t.member_login_name) AS total, \n")
			.append("(SELECT path FROM t_tender_bid_doc WHERE tender_id = t.tender_id ) AS path, \n")			
			.append("(SELECT status_name FROM v_tender_information WHERE tender_id= t.tender_id ) AS status_name, \n")	
			.append("t.member_login_name, \n")
			.append("t.tender_id \n")		
			.append("from \n")
			.append(" v_member m \n")			
			.append(" ,t_tender_bid t \n")
			.append(" where \n")
			.append(" m.member_login_name=t.member_login_name \n")
			.append(" and t.tender_id= ? \n")
			.append(" and t.delete_flag= false  \n");
			if (logger.isTraceEnabled()) {
				logger.trace("[TenderBidDao_JDBCImpl#findTenderBidSuppliers] sql={}", sql);
			}
			
			Connection connection = DatabaseSessionManager.getInstance().getSession();
			statement = connection.prepareStatement(sql.toString());
			statement.setInt(1, tenderid);
			ResultSet resultSet = statement.executeQuery();
			ResultSetMapper<TenderBidlist> resultSetMapper=new ResultSetMapper<TenderBidlist>();
			List<TenderBidlist> tenderBidList=resultSetMapper.mapResultSetToList(resultSet, TenderBidlist.class);
			return tenderBidList;
		}catch(SQLException e){
			if (logger.isTraceEnabled()) {
				logger.trace("[TenderBidDao_JDBCImpl#findTenderBidSuppliers] sql error occurred.", e);
			}
			throw new DatabaseException("database error occurred.", e);
		}finally{
			if (logger.isInfoEnabled()) {
				logger.info("[TenderBidDao_JDBCImpl#findTenderBidSuppliers] finish.");
			}
			if (statement != null) { 
				try { 
					statement.close(); 
					} catch (SQLException e) {} 
				}
		}
	}

	@Override
	public void updateTenderInformation(Integer tenderid) {
		if(logger.isInfoEnabled()){
			logger.info("[TenderBidDao_JDBCImpl#updateTenderInformation] start.");
		}
		PreparedStatement statement=null;
		try{
	
			StringBuffer sql=new StringBuffer()
			.append("update \n")
			.append(" t_tender_information \n")
			.append(" set \n")
			.append(" status = ? \n")
			.append(" where \n")
			.append(" tender_id = ?\n");
			
			if(logger.isTraceEnabled()){
				logger.trace("[TenderBidDao_JDBCImpl#updateTenderInformation] sql={}",sql);
			}
			Connection connection = DatabaseSessionManager.getInstance()
					.getSession();
			statement = connection.prepareStatement(sql.toString());
			statement.setInt(1, 7);
			statement.setInt(2, tenderid);
			statement.executeUpdate();
			
		}catch(SQLException e){
			if(logger.isTraceEnabled()){
				logger.trace("[TenderBidDao_JDBCImpl#updateTenderInformation] sql error occurred.",
						e);
			}
			throw new DatabaseException("database error occurred.", e);
		}finally{
			if (logger.isInfoEnabled()) {
				logger.info("[TenderBidDao_JDBCImpl#updateTenderInformation] finish.");
			}
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
				}
			}
		}
		
	}

	@Override
	public void deleteTenderBid(Integer tenderid, String menmberName) {
		if(logger.isInfoEnabled()){
			logger.info("[TenderBidDao_JDBCImpl#deleteTenderBid] start.");
		}
		PreparedStatement statement=null;
		try{
	
			StringBuffer sql=new StringBuffer()
			.append("delete from \n")
			.append(" t_tender_bid \n")			
			.append(" where \n")
			.append(" tender_id = ?\n")
			.append(" and member_login_name = ?\n");
			
			if(logger.isTraceEnabled()){
				logger.trace("[TenderBidDao_JDBCImpl#deleteTenderBid] sql={}",sql);
			}
			Connection connection = DatabaseSessionManager.getInstance()
					.getSession();
			statement = connection.prepareStatement(sql.toString());			
			statement.setInt(1, tenderid);
			statement.setString(2, menmberName);
			statement.executeUpdate();
			
		}catch(SQLException e){
			if(logger.isTraceEnabled()){
				logger.trace("[TenderBidDao_JDBCImpl#deleteTenderBid] sql error occurred.",
						e);
			}
			throw new DatabaseException("database error occurred.", e);
		}finally{
			if (logger.isInfoEnabled()) {
				logger.info("[TenderBidDao_JDBCImpl#deleteTenderBid] finish.");
			}
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
				}
			}
		}		
		
	}

	@Override
	public void uodateTenderQuoteFlag(Integer tenderid, String menmberName) {
		if(logger.isInfoEnabled()){
			logger.info("[TenderBidDao_JDBCImpl#uodateTenderQuoteFlag] start.");
		}
		PreparedStatement statement=null;
		try{
	
			StringBuffer sql=new StringBuffer()
			.append("update \n")
			.append(" t_tender_quote \n")
			.append(" set \n")
			.append(" bid_flag = ? \n")
			.append(" where \n")
			.append(" tender_id = ?\n")
			.append(" and member_login_name = ?\n");
			
			if(logger.isTraceEnabled()){
				logger.trace("[TenderBidDao_JDBCImpl#uodateTenderQuoteFlag] sql={}",sql);
			}
			Connection connection = DatabaseSessionManager.getInstance()
					.getSession();
			statement = connection.prepareStatement(sql.toString());
			statement.setBoolean(1, false);
			statement.setInt(2, tenderid);
			statement.setString(3, menmberName);
			statement.executeUpdate();
			
		}catch(SQLException e){
			if(logger.isTraceEnabled()){
				logger.trace("[TenderBidDao_JDBCImpl#uodateTenderQuoteFlag] sql error occurred.",
						e);
			}
			throw new DatabaseException("database error occurred.", e);
		}finally{
			if (logger.isInfoEnabled()) {
				logger.info("[TenderBidDao_JDBCImpl#uodateTenderQuoteFlag] finish.");
			}
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
				}
			}
		}		
	}

}
